unit MLPAdm;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB;

type
  TProbeInfo = class
    id:string;
    order:string;
    length:integer;
    chrpos:string;
    cytoband:string;
    gene:string;
    genesub:string;
    probe:string;
    control:boolean;
    qctype:integer;
  end;

  TDM = class(TDataModule)
    connMLPAexp: TADOConnection;
    tblSamples: TADOTable;
    dsSamples: TDataSource;
    tblKits: TADOTable;
    tblProbes: TADOTable;
    dsKits: TDataSource;
    dsProbes: TDataSource;
    qryGetKitId: TADOQuery;
    qryGetProbeId: TADOQuery;
    tblRuns: TADOTable;
    qryAssayCount: TADOQuery;
    dsRuns: TDataSource;
    qrySelectRun: TADOQuery;
    dsSelectRun: TDataSource;
    tblAssays: TADOTable;
    tblSamplessampleid: TAutoIncField;
    tblSamplesLabId: TWideStringField;
    tblSamplesvolgnrblok: TWideStringField;
    tblSamplestypeID: TIntegerField;
    tblSamplesnormaaltumor: TWideStringField;
    tblSamplespercentagetumor: TIntegerField;
    tblSamplesnecrotisch: TWideStringField;
    tblSamplesopmerkingen: TWideStringField;
    tblSamplesdatum: TDateTimeField;
    qrySelectAssay: TADOQuery;
    dsSelectAssay: TDataSource;
    qryGetKitIDs: TADOQuery;
    tblAssayProbes: TADOTable;
    qrySampleAssays: TADOQuery;
    dsSampleAssays: TDataSource;
    qryRunAssays: TADOQuery;
    tblRunsrunid: TAutoIncField;
    tblRunsGelfileName: TWideStringField;
    tblRunsTechnician: TWideStringField;
    tblRunsDate: TDateTimeField;
    tblRunsProbeMixStockno: TWideStringField;
    tblRunsBufferStockno: TWideStringField;
    tblRunsLigaseStockNo: TWideStringField;
    dsRunAssays: TDataSource;
    tblAnalyses: TADOTable;
    dsAnalyses: TDataSource;
    qryAnalysisAssays: TADOQuery;
    dsAnalysisAssays: TDataSource;
    qryKitAssays: TADOQuery;
    dsKitAssays: TDataSource;
    tblAnalysisAssays: TADOTable;
    qryGetAnalysisAssay: TADOQuery;
    qryGetAnalysisProbeCount: TADOQuery;
    qryGetAnalysisAssayCount: TADOQuery;
    qryAssayProbes: TADOQuery;
    tblAnalysisProbes: TADOTable;
    qryGetKitProbeNummers: TADOQuery;
    qryAnalysisProbes: TADOQuery;
    qrySampleInfo: TADOQuery;
    qryRunSampleInfo: TADOQuery;
    qryCustom: TADOQuery;
    qryRunExport: TADOQuery;
    qryRunExportSampleIDField: TWideStringField;
    qryRunExportPlateWell: TWideStringField;
    qryRunExportComment: TWideStringField;
    qryRunExportLabId: TWideStringField;
    qryGetProbeLengthId: TADOQuery;
    tblKitalias: TADOTable;
    qryAlias2kitid: TADOQuery;
    dsKitalias: TDataSource;
    qryRunExportkitid: TWideStringField;
    qrySamplesheet: TADOQuery;
    WideStringField1: TWideStringField;
    WideStringField2: TWideStringField;
    WideStringField3: TWideStringField;
    WideStringField4: TWideStringField;
    WideStringField5: TWideStringField;
    tblAnalysesanalysisid: TAutoIncField;
    tblAnalyseskitid: TWideStringField;
    tblAnalysesdate: TDateTimeField;
    tblAnalysesTechnician: TWideStringField;
    tblAnalysesexcludedprobes: TWideStringField;
    tblAnalysescontrolprobes: TWideStringField;
    tblAnalysesstableprobes: TWideStringField;
    tblAnalysesgroupprobes: TWideStringField;
    tblAnalysestresholdlow: TFloatField;
    tblAnalysestresholdhigh: TFloatField;
    tblAnalysescolorlow: TIntegerField;
    tblAnalysescolorhigh: TIntegerField;
    tblAnalysescenterwhite: TBooleanField;
    tblAnalysesauthorized: TBooleanField;
    tblAnalysesrawdatatype: TSmallintField;
    tblAnalysesusecontrolprobes: TBooleanField;
    tblAnalysesusecontrolsamples: TBooleanField;
    tblAnalysesmedianpolish: TSmallintField;
    procedure DataModuleCreate(Sender: TObject);
    procedure connMLPAexpConnectComplete(Connection: TADOConnection;
      const Error: Error; var EventStatus: TEventStatus);
    procedure tblSamplesAfterInsert(DataSet: TDataSet);
    procedure tblSamplesAfterPost(DataSet: TDataSet);
  private
    FExpDatabaseMDB: string;
    FKitDescription: string;
    FKitDesigner: string;
    FInsertedSample : boolean;
    procedure SetExpDatabaseMDB(const Value: string);
    { Private declarations }
  public
    { Public declarations }
    function KitExists(kitid:string):boolean;
    function GenotyperProbeExists(kitid,probeid:string):boolean;
    function MarkerLengthToId(kitid,length:string):string;
    function SampleInfoExists(runid:integer;sampleinfo:string):boolean;
    function AssayCount(runid:integer):integer;
    function AnalysisAssaysEmpty(assayid,analysisid:integer) : boolean;
    function getAnalysisAssayCount(analysisid:integer):integer;
    function getAnalysisProbeCount(analysisassayid:integer):integer;

    procedure GetKitIDList(list : TStrings);
    procedure GetProbeIDList(kitid:string;list,QC : TStrings);
    function KitAlias2ID(alias:string):string;
    procedure AddRun(run:string);
    property ExpDatabaseMDB : string read FExpDatabaseMDB write SetExpDatabaseMDB;
    property KitDescription : string read FKitDescription;
    property Kitdesigner : string read FKitDesigner;
  end;

var
  DM: TDM;

implementation

{$R *.DFM}
const
  ConnectionString1 = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';
  ConnectionString2 = ';Persist Security Info=False';
{ TDM }

procedure TDM.SetExpDatabaseMDB(const Value: string);
begin
  connMLPAExp.Connected:=False;
  FExpDatabaseMDB := Value;
  ConnMLPAexp.ConnectionString:=ConnectionString1+value+ConnectionString2;
  connMLPAExp.Connected:=True;
end;

procedure TDM.DataModuleCreate(Sender: TObject);
var
  ApplicationPath:string;
begin
 ApplicationPath:=ExtractFileDir(Application.ExeName);
 ExpDatabaseMDB:=ApplicationPath+'\doc\MLPAdatabase.mdb';
end;

procedure TDM.connMLPAexpConnectComplete(Connection: TADOConnection;
  const Error: Error; var EventStatus: TEventStatus);
begin
  if EventStatus=esOK then
  begin
    tblAssayprobes.Active:=true;
    tblAssays.Active:=True;
    tblAnalysisProbes.Active:=True;
    tblAnalysisAssays.Active:=True;
    tblAnalyses.Active:=True;
    tblSamples.Active:=True;
    tblRuns.Active:=True;
    tblKits.Active:=True;
    tblKitalias.Active:=true;
    tblProbes.Active:=True;
    qrySampleAssays.Active:=True;
    qryRunAssays.Active:=True;
    qryKitAssays.Active:=True;
    qrySelectRun.Active:=True;
    qrySelectAssay.Active:=True;
    qryAnalysisAssays.Active:=True;
  end;
end;

function TDM.KitAlias2ID(alias: string): string;
begin
  result:=alias;
  qryAlias2kitid.Parameters[0].Value:=alias;
  qryAlias2kitid.Open;
  if not qryAlias2kitid.Eof then
    result:=qryAlias2kitid.Fields[0].AsString;
  qryAlias2kitid.Close;
end;

function TDM.KitExists(kitid: string): boolean;
begin
  if connMLPAExp.Connected then
  begin
    qryGetKitId.Parameters[0].Value:=Trim(kitid);
    qryGetKitId.Open;
    if qryGetKitId.Eof then
    begin
      result:=false;
      fKitDescription:='';
      FKitDesigner:='';
    end
    else begin
      result:=true;
      fKitDescription:=qryGetKitId.FieldValues['KitDescription'];
      FKitDesigner:=qryGetKitId.FieldValues['Ontwerper'];
    end;
    qryGetKitId.Close;
  end
  else
    result:=true;
end;

function TDM.MarkerLengthToId(kitid, length: string): string;
begin
  if connMLPAExp.Connected then
  begin
    qryGetProbeLengthId.Parameters[0].Value:=Trim(kitid);
    qryGetProbeLengthId.Parameters[1].Value:=StrToInt(length);
    qryGetProbeLengthId.Open;
    if qryGetProbeLengthId.Eof then
    begin
      result:='';
    end
    else begin
      result:=qryGetProbeLengthId.Fields[0].AsString;
    end;
    qryGetProbeLengthId.Close;
  end
  else
    result:='';
end;

function TDM.GenotyperProbeExists(kitid, probeid: string): boolean;

begin
  if connMLPAExp.Connected then
  begin
    qryGetProbeId.Parameters[0].Value:=Trim(kitid);
    qryGetProbeId.Parameters[1].Value:=Trim(probeid);
    qryGetProbeId.Open;
    if qryGetProbeId.Eof then
    begin
      result:=false;
    end
    else begin
      result:=true;
    end;
    qryGetProbeId.Close;
  end
  else
    result:=false;
end;

function TDM.AssayCount(runid: integer): integer;
begin
  if connMLPAexp.Connected then
  begin
    qryAssayCount.Parameters[0].Value:=runid;
    qryAssayCount.Open;
    if qryAssayCount.Eof then
    begin
      result:=-1;
    end
    else begin
      result:=qryAssayCount.Fields[0].Value;
    end;
    qryAssayCount.Close;
  end
  else
    result:=0;
end;

procedure TDM.AddRun(run: string);
var
 NotActive: boolean;
begin
  if not tblRuns.Active then
  begin
    tblRuns.Open;
    NotActive:=True;
  end
  else
    NotActive:=false;
  tblRuns.InsertRecord([nil,run,nil,Now]);
  if NotActive then
    tblRuns.Close;
end;

procedure TDM.GetKitIDList(list: TStrings);
begin
  list.Clear;
  if connMLPAExp.Connected then
  begin
    qryGetKitIDs.Open;
    while not qryGetKitIDs.Eof do
    begin
      list.Add(qryGetKitIDs.Fields[0].AsString);
      qryGetKitIDs.Next;
    end;
    qryGetKitIDs.Close;
  end;
end;

procedure TDM.GetProbeIDList(kitid:string;list,QC: TStrings);
var
  ProbeInfo : TProbeInfo;
  I: Integer;
begin
  for I := 0 to List.Count - 1 do
  begin
    list.Objects[i].Free;
    list.Objects[i]:=nil;
  end;
  list.Clear;
  if Assigned(QC) then
  begin
    for i := 0 to QC.Count - 1 do
    begin
      QC.Objects[i].Free;
      QC.Objects[i]:=nil;
    end;
    QC.clear;
  end;
  if connMLPAExp.Connected then
  begin
    qryGetKitProbeNummers.Parameters[0].Value:=kitid;
    qryGetKitProbeNummers.Open;
    while not qryGetKitProbeNummers.Eof do
    begin
      ProbeInfo:=TProbeInfo.Create;
      ProbeInfo.id:=qryGetKitProbeNummers.FieldByName('id').AsString;
      ProbeInfo.order:=qryGetKitProbeNummers.FieldByName('order').AsString;
      ProbeInfo.length:=qryGetKitProbeNummers.FieldByName('length').AsInteger;
      ProbeInfo.chrpos:=qryGetKitProbeNummers.FieldByName('chrpos').AsString;
      ProbeInfo.cytoband :=qryGetKitProbeNummers.FieldByName('cytoband').AsString;
      ProbeInfo.gene:=qryGetKitProbeNummers.FieldByName('gene').AsString;
      ProbeInfo.genesub:=qryGetKitProbeNummers.FieldByName('genesub').AsString;
      ProbeInfo.probe:=qryGetKitProbeNummers.FieldByName('probe').AsString;
      ProbeInfo.control:=qryGetKitProbeNummers.FieldByName('control').AsBoolean;
      ProbeInfo.qctype:=qryGetKitProbeNummers.FieldByName('type').AsInteger;
      if ProbeInfo.qctype=1 then
        list.AddObject(ProbeInfo.id,ProbeInfo)
      else if Assigned(QC) then
        QC.AddObject(ProbeInfo.id,ProbeInfo)
      else
        ProbeInfo.Free;
      qryGetKitProbeNummers.Next;
    end;
    qryGetKitProbeNummers.Close;
  end;
end;


function TDM.AnalysisAssaysEmpty(assayid, analysisid: integer): boolean;
begin
  qryGetAnalysisAssay.Parameters[0].Value:=assayid;
  qryGetAnalysisAssay.Parameters[1].Value:=analysisid;
  qryGetAnalysisAssay.Open;
  result:=qryGetAnalysisAssay.IsEmpty;
  qryGetAnalysisAssay.Close;
end;

function TDM.getAnalysisAssayCount(analysisid: integer): integer;
begin
  qryGetAnalysisAssayCount.Parameters[0].Value:=analysisid;
  qryGetAnalysisAssayCount.Open;
  result:=qryGetAnalysisAssayCount.Fields[0].AsInteger;
  qryGetAnalysisAssayCount.Close;
end;

function TDM.getAnalysisProbeCount(analysisassayid: integer): integer;
begin
  qryGetAnalysisProbeCount.Parameters[0].Value:=analysisassayid;
  qryGetAnalysisProbeCount.Open;
  result:=qryGetAnalysisProbeCount.Fields[0].AsInteger;
  qryGetAnalysisProbeCount.Close;
end;

procedure TDM.tblSamplesAfterInsert(DataSet: TDataSet);
begin
  FInsertedSample:=True;
end;

procedure TDM.tblSamplesAfterPost(DataSet: TDataSet);
begin
  //if FInsertedSample then
  //  tblSamples.Refresh;
  FInsertedSample:=false;
end;

function TDM.SampleInfoExists(runid: integer; sampleinfo: string): boolean;
begin
  if connMLPAExp.Connected then
  begin
    qryRunSampleInfo.Parameters[0].Value:=runid;
    qryRunSampleInfo.Parameters[1].Value:=sampleinfo;
    qryRunSampleInfo.Open;

    if qryRunSampleInfo.Eof then
    begin
      result:=false;
    end
    else begin
      result:=true;
    end;
    qryRunSampleInfo.Close;
  end
  else
    result:=true;
end;

end.
